package com.coderman.daoclient.connections;

import com.coderman.daoclient.annotations.Column;
import com.coderman.daoclient.annotations.Table;
import com.coderman.daoclient.annotations.UseTransaction;
import com.coderman.daoclient.bean.DBBean;
import com.coderman.daoclient.bean.KVPair;
import com.coderman.daoclient.enums.FieldTypeEnum;
import com.coderman.daoclient.exceptions.IdNullException;
import com.coderman.daoclient.reflect.BeanMapInfo;
import com.coderman.daoclient.reflect.FieldInfo;
import com.coderman.daoclient.utils.ClassUtils;
import com.coderman.daoclient.utils.JdbcUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * Created by ${fanchunshuai} on 2017-7-27.
 *
 * @author 58
 * @version: V1.0
 * @Desc:
 * @Copyright (c) 2017 58到家-版权所有
 */
public class StatementTask extends AbstractStatementTask {
    private Logger logger = LoggerFactory.getLogger(this.getClass());
    @Override
    public Long exeInsert(Object o) throws Exception {
        List<FieldInfo> fieldInfoList = ClassUtils.getBean().get(o.getClass());
        StringBuffer buffer = new StringBuffer("INSERT INTO ");
        Table table = o.getClass().getAnnotation(Table.class);
        if(table == null){
            throw  new NullPointerException("entity not have annotation of Table:"+o.getClass().getName());
        }
        buffer.append(table.name());

        StringBuffer columnBuff = new StringBuffer(" (");

        StringBuffer valueBuff = new StringBuffer(" VALUES (");

        for (FieldInfo fieldInfo : fieldInfoList){
            Method method = o.getClass().getMethod(fieldInfo.getGetMethodName());
            Object object = method.invoke(o);
            if(!Objects.isNull(object) && fieldInfo.getIsID() == 0){
                columnBuff.append(fieldInfo.getColName()+",");
                valueBuff.append("?,");
                fieldInfo.setFieldValue(object);
            }
        }

        String cv = columnBuff.substring(0,columnBuff.length() - 1)+") " + valueBuff.substring(0,valueBuff.length() - 1)+")" ;
        buffer.append(cv);
        logger.info("insertSql = "+buffer.toString());
        PreparedStatement statement = super.createPreparedStatement(buffer.toString(),Statement.RETURN_GENERATED_KEYS);
        try {
            realExeUpdateSql(fieldInfoList,statement);
        }catch (Exception e){
            e.printStackTrace();
            logger.error("insert bean error ",e);
            return -1L;
        }
        ResultSet rs = statement.getGeneratedKeys();
        long id = 0;
        if (rs.next()){
            id = rs.getLong(1);
        }
        JdbcUtils.closeStatement(statement);
        return id;
    }

    @Override
    public void exeUpdate(Object o) throws Exception {
        List<FieldInfo> fieldInfoList = ClassUtils.getBean().get(o.getClass());
        StringBuffer buffer = new StringBuffer("UPDATE  ");
        Table table = o.getClass().getAnnotation(Table.class);
        if(Objects.isNull(table) || "".equals(table.name())){
            throw  new NullPointerException("entity not have annotation of Table:"+o.getClass().getName());
        }
        buffer.append(table.name() + " SET ");
        String whereId = "";
        for (FieldInfo fieldInfo : fieldInfoList){

            Method method = o.getClass().getMethod(fieldInfo.getGetMethodName());
            Object object = method.invoke(o);

            if(!Objects.isNull(object) && fieldInfo.getIsID() == 0){
                fieldInfo.setFieldValue(object);
                buffer.append(fieldInfo.getColName()+"=?,");
            }
            //id处理
            if(fieldInfo.getIsID() == 1){
                if(Objects.isNull(object)){
                    logger.error("id value is null",new NullPointerException());
                    throw new IdNullException("id value is null ",new NullPointerException());
                }
                whereId = " WHERE " + fieldInfo.getColName() + "=" + object.toString();
            }
        }
        String sql = buffer.substring(0,buffer.length() - 1);
        buffer = new StringBuffer(sql);
        buffer.append(whereId);
        logger.info("updateSql : "+buffer.toString());
        PreparedStatement statement = super.createPreparedStatement(buffer.toString());
        realExeUpdateSql(fieldInfoList,statement);
        JdbcUtils.closeStatement(statement);
    }


    @Override
    public Map<String, String> getMap(Class clazzBean, String keyColumn, String valueColumn) throws Exception {
        String tableName = BeanMapInfo.getTableMap().get(clazzBean);
        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+clazzBean.getClass().getName());
        }
        StringBuffer buffer = new StringBuffer("SELECT ");
        buffer.append(keyColumn);
        buffer.append(","+valueColumn);
        buffer.append(" FROM ");
        buffer.append(tableName);
        PreparedStatement statement = super.createPreparedStatement(buffer.toString());
        Map<String,String> map = new HashMap<>();
        ResultSet set = statement.executeQuery();
        while (set.next()){
            String key = set.getObject(1).toString();
            if(set.getObject(2) != null){
                String value = set.getObject(2).toString();
                map.put(key,value);
            }else {
                map.put(key,"");
            }

        }

        return map;
    }

    @Override
    public List getList(Class clazzBean, String sql) throws Exception {
        System.out.println("sql = "+sql);
        PreparedStatement statement = super.createPreparedStatement(sql);
        return    realExeQueryList(clazzBean,statement);
    }

    @Override
    public List getList(Class clazz, List list) throws Exception {
        String tableName = BeanMapInfo.getTableMap().get(clazz);
        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+clazz.getClass().getName());
        }

        List<FieldInfo> fieldInfoList = BeanMapInfo.getFieldMap().get(clazz);
        StringBuffer buffer = new StringBuffer("SELECT  ");

        for (FieldInfo fieldInfo : fieldInfoList){
            buffer.append(fieldInfo.getColName()+" ,");
        }

        buffer = new StringBuffer(buffer.substring(0,buffer.length() - 1));
        buffer.append(" FROM ");

        buffer.append(tableName);

        if( !Objects.isNull(list) && !list.isEmpty()){
            buffer.append(" WHERE 1=1  ");
            for (int i = 0;i < list.size();i++){
                KVPair kvPair = (KVPair) list.get(i);
                buffer.append(" AND "+ kvPair.getKey()+"=?  ");
            }

            PreparedStatement statement = super.createPreparedStatement(buffer.toString());

            for (int i = 0;i < list.size();i++){
                KVPair kvPair = (KVPair) list.get(i);
                convertParamValue(kvPair.getValue(),(i + 1),statement);
            }
            return realExeQueryList(clazz,statement,fieldInfoList);
        }
        logger.info("selectSql : "+buffer.toString());
        PreparedStatement statement = super.createPreparedStatement(buffer.toString());
        return realExeQueryList(clazz,statement,fieldInfoList);
    }

    @Override
    public Long exeQueryCount(Class clazz, List list) throws Exception {
        if(list == null){
            return exeQueryCount(clazz);
        }
        StringBuffer buffer = new StringBuffer("SELECT COUNT(1) FROM ");

        String tableName = BeanMapInfo.getTableMap().get(clazz);
        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+clazz.getClass().getName());
        }
        buffer.append(tableName);
        buffer.append(" WHERE 1=1  ");
        for (int i = 0;i < list.size();i++){
            KVPair kvPair = (KVPair) list.get(i);
            buffer.append(" AND "+ kvPair.getKey()+"=?  ");
        }
        PreparedStatement statement = super.createPreparedStatement(buffer.toString());

        logger.info("selectSql : "+buffer.toString());
        for (int i = 0;i < list.size();i++){
            KVPair kvPair = (KVPair) list.get(i);
            convertParamValue(kvPair.getValue(),(i + 1),statement);
        }
        Long count =  realExeQuery(Long.class,statement,null);
        return count;
    }

    @Override
    public Long exeQueryCount(Class clazz) throws Exception {
        StringBuffer buffer = new StringBuffer("SELECT COUNT(1) FROM ");

        String tableName = BeanMapInfo.getTableMap().get(clazz);
        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+clazz.getClass().getName());
        }
        buffer.append(tableName);
        PreparedStatement statement = super.createPreparedStatement(buffer.toString());
        Long count =  realExeQuery(Long.class,statement,null);
        return count;
    }

    @Override
    public Object exeQueryTID(Class clazz, Object id) throws Exception {
        String tableName = BeanMapInfo.getTableMap().get(clazz);

        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+clazz.getClass().getName());
        }

        List<FieldInfo> fieldInfoList = BeanMapInfo.getFieldMap().get(clazz);
        StringBuffer buffer = new StringBuffer("SELECT  ");

        for (FieldInfo fieldInfo : fieldInfoList){
            buffer.append(fieldInfo.getColName()+" ,");
        }

        buffer = new StringBuffer(buffer.substring(0,buffer.length() - 1));

        buffer.append(" FROM ");
        buffer.append(tableName);
        buffer.append(" WHERE ");
        buffer.append(" id = ? limit 1");
        logger.info("selectSql : "+buffer.toString());

        PreparedStatement statement = super.createPreparedStatement(buffer.toString());
        statement.setObject(1,id);
        return realExeQuery(clazz,statement,fieldInfoList);

    }


    private <T> T realExeQuery(Class<?> clazz,PreparedStatement statement,List<FieldInfo> fieldInfoList) throws Exception {
        ResultSet set = statement.executeQuery();

        if(fieldInfoList ==null && set.next()){
            return (T) set.getObject(1);
        }

        T t = (T) clazz.getConstructor(new Class[] {}).newInstance();
        if(set.next()){
            for (FieldInfo fieldInfo : fieldInfoList){
                Method setMethod = clazz.getMethod(fieldInfo.getSetMethodName(),new Class[] {FieldTypeEnum.getType(fieldInfo.getColType())});
                setMethod.invoke(t, new Object[] { set.getObject(fieldInfo.getColName())});// 调用对象的setXXX方法
            }
        }
        return t;
    }

    private <T> List<T> realExeQueryList(Class<T> clazz,PreparedStatement statement) throws Exception {
        List<T> list = new ArrayList<T>();
        Field [] fields  = clazz.getDeclaredFields();
        ResultSet rs = statement.executeQuery();
        Map<String,String> map = new HashMap<>();
        int count = rs.getMetaData().getColumnCount();

        for (int i = 0;i< count;i++){
            map.put(rs.getMetaData().getColumnName(i+1),rs.getMetaData().getColumnName(i+1));
        }

        List<FieldInfo> fieldInfoList = BeanMapInfo.getFieldMap().get(clazz);

        // 循环读取结果集的每一行的每一列
        while (rs.next()){
            // 获得无参构造对象方法创建实例
            T t = (T) clazz.getConstructor(new Class[] {}).newInstance();
            for (FieldInfo field : fieldInfoList) {
                String colName = field.getColName();

                if(Objects.isNull(map.get(colName))){
                    continue;
                }

                Object value = getResultSetValue(rs,field.getColType(),field.getColName());

                // 获得属性的首字母并转换为大写，与setXXX对应
                String firstLetter = field.getFieldName().substring(0, 1).toUpperCase();

                String setMethodName = "set" + firstLetter
                        + field.getFieldName().substring(1);
                Method setMethod = clazz.getMethod(setMethodName,
                        new Class[] { FieldTypeEnum.getType(field.getColType()) });
                setMethod.invoke(t, new Object[] { value });// 调用对象的setXXX方法
            }
            list.add(t);
        }
        return list;
    }

    private <T> List<T> realExeQueryList(Class<?> clazz,PreparedStatement statement,List<FieldInfo> fieldInfoList) throws Exception {
        List<T> list = new ArrayList<T>();
        ResultSet set = statement.executeQuery();

        while (set.next()){
            T t = (T) clazz.getConstructor(new Class[] {}).newInstance();
            for (FieldInfo fieldInfo : fieldInfoList){
                Method setMethod = clazz.getMethod(fieldInfo.getSetMethodName(),new Class[] {FieldTypeEnum.getType(fieldInfo.getColType())});
                setMethod.invoke(t, new Object[] { set.getObject(fieldInfo.getColName())});// 调用对象的setXXX方法
            }
            list.add(t);
        }
        return list;
    }

    @Override
    public void exeUpdate(Object o, List list) throws Exception {

        String tableName = BeanMapInfo.getTableMap().get(o.getClass());
        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+o.getClass().getName());
        }

        StringBuffer buffer = new StringBuffer("UPDATE ");
        buffer.append(tableName);
        buffer.append(" SET ");
        for (int i = 0;i < list.size();i++){
            KVPair kvPair = (KVPair) list.get(i);
            buffer.append(kvPair.getKey()+"=?,");
        }

        String sql = buffer.substring(0,buffer.length() - 1);
        logger.info("selectSql : "+sql);

        PreparedStatement statement = super.createPreparedStatement(sql);

        for (int i = 0;i < list.size();i++){
            KVPair kvPair = (KVPair) list.get(i);
            convertParamValue(kvPair.getValue(),(i + 1),statement);
        }

        realExeUpdateSql(null,statement);
        JdbcUtils.closeStatement(statement);
    }

    @Override
    public void exeUpdate(Class clazz, List list, KVPair idPair) throws Exception {
        String tableName = BeanMapInfo.getTableMap().get(clazz);
        if(Objects.isNull(tableName) || "".equals(tableName)){
            throw  new NullPointerException("entity not have annotation of Table:"+clazz.getClass().getName());
        }
        if(Objects.isNull(idPair)){
            throw  new NullPointerException("entity idPair is null.....");
        }

        StringBuffer buffer = new StringBuffer("UPDATE ");
        buffer.append(tableName);
        buffer.append(" SET ");
        for (int i = 0;i < list.size();i++){
            KVPair kvPair = (KVPair) list.get(i);
            buffer.append(kvPair.getKey()+"=?,");
        }


        String sql = buffer.substring(0,buffer.length() - 1)+" WHERE "+idPair.getKey()+"=?";

        logger.info("selectSql : "+sql);

        PreparedStatement statement = super.createPreparedStatement(sql);

        for (int i = 0;i < list.size();i++){
            KVPair kvPair = (KVPair) list.get(i);
            convertParamValue(kvPair.getValue(),(i + 1),statement);
        }

        convertParamValue(idPair.getValue(),list.size() + 1,statement);

        realExeUpdateSql(null,statement);
        JdbcUtils.closeStatement(statement);
    }



    @Override
    public void exeDelete(Class clazz, Object id) throws Exception {
        StringBuffer buffer = new StringBuffer("DELETE  FROM  ");
        String  tName = BeanMapInfo.getTableMap().get(clazz);
        buffer.append(tName);
        buffer.append("  WHERE id = ?");
        logger.info("delete sql : "+buffer.toString());
        PreparedStatement statement = super.createPreparedStatement(buffer.toString());
        statement.setObject(1,id);
        realExeUpdateSql(null,statement);
        JdbcUtils.closeStatement(statement);
    }

    /**
     * 开启事务
     * @throws SQLException
     */
    @Override
    public void beginTransaction() throws SQLException {
        super.beginTransaction();
    }

    /**
     * 关闭事务并归还连接到连接池
     * @throws SQLException
     */
    @Override
    public void commitTransaction() throws SQLException {
        super.commitTransaction();
    }

    /**
     * 回滚事务
     * @throws SQLException
     */
    @Override
    public void rollBackTransaction() throws SQLException {
      super.rollBackTransaction();
    }


    private void realExeUpdateSql(List<FieldInfo> fieldInfoList,PreparedStatement statement) throws SQLException {
        if(fieldInfoList != null ){
            int index = 1;
            for (FieldInfo fieldInfo : fieldInfoList){
                if(!Objects.isNull(fieldInfo.getFieldValue()) && fieldInfo.getIsID() == 0){
                    setPreParams(fieldInfo.getFieldValue(),fieldInfo.getColType(),index,statement);
                    index++;
                }
            }
        }
        statement.execute();

    }



    private void setPreParams(Object value,int colType,int index, PreparedStatement ps) throws SQLException {

        switch (colType){
            //int --- Integer
            case 3:
                ps.setInt(index,(Integer) value);
                break;
            case 4:
                ps.setInt(index,(Integer) value);
                break;

            //String
            case 5:
                ps.setString(index,(String) value);
                break;
            //long --- Long
            case 10:
                ps.setLong(index,(Long) value);
                break;
            case 11:
                ps.setLong(index,(Long) value);
                break;
            //Date
            case 14:
                ps.setObject(index, value);
                break;
            default:
                System.out.println("error......");

        }
    }


    private Object getResultSetValue(ResultSet rs,Field field) throws SQLException {

        String fieldName = field.getName();
        Object value = null;

        if(field.getType().equals(int.class) || field.getType().equals(Integer.class)){
            value = rs.getInt(fieldName);
        }
        else if(field.getType().equals(long.class) || field.getType().equals(Long.class)){
            value = rs.getLong(fieldName);
        }
        else if(field.getType().equals(java.lang.String.class)){
            value = rs.getString(fieldName);
        }
        else if(field.getType().equals(java.util.Date.class)){
            value = rs.getDate(fieldName);
        }
        return value;
    }


    private Object getResultSetValue(ResultSet rs,int colType,String fieldName) throws SQLException {


        Object value = null;

        /*if(type.getClass().equals(int.class) || type.getClass().equals(Integer.class)){
            value = rs.getInt(fieldName);
        }
        else if(type.getClass().equals(long.class) || type.getClass().equals(Long.class)){
            value = rs.getLong(fieldName);
        }
        else if(type.getClass().equals(java.lang.String.class)){
            value = rs.getString(fieldName);
        }
        else if(type.getClass().equals(java.util.Date.class)){
            value = rs.getDate(fieldName);
        }
        return value;*/



        switch (colType){
            //int --- Integer
            case 3:
                value = rs.getInt(fieldName);
                break;
            case 4:
                value = rs.getInt(fieldName);
                break;

            //String
            case 5:
                value = rs.getString(fieldName);
                break;
            //long --- Long
            case 10:
                value = rs.getLong(fieldName);
                break;
            case 11:
                value = rs.getLong(fieldName);
                break;
            //Date
            case 14:
                value = rs.getDate(fieldName);
                break;
            default:
                System.out.println("error......");

        }
        return value;
    }


    private void convertParamValue(Object value,int index,PreparedStatement ps ) throws SQLException {
        if(value instanceof String){
            ps.setString(index,(String) value);
        }

        else if(value instanceof Date){
            ps.setObject(index,value);
        }

        else if(value instanceof Long){
            ps.setLong(index,(Long) value);
        }

        else if(value instanceof Integer){
            ps.setInt(index,(Integer) value);
        }
    }


    public static void main(String[] args) {
        StatementTask task = new StatementTask();
        DBBean bean = new DBBean();
        bean.setAge(37);
        bean.setCreateTime(new Date());
        bean.setName("我是程序男，coderman");
        bean.setValue("程序男的博客");
        try {
            //task.beginTransaction();
            //long  id = task.exeInsert(bean);
            //System.out.printf("id = "+id);
            //task.exeInsert(bean);
            //task.exeInsert(bean);
            //int a  = 3/0;
            //task.exeInsert(bean);

            //task.commitTransaction();

            //task.exeUpdate(bean);
            //task.exeDelete(DBBean.class,";drop table...");
            List<KVPair<String,Object>> list = new ArrayList<>();
            list.add(KVPair.build("name","abcdefg"));
            list.add(KVPair.build("age",23));
            list.add(KVPair.build("create_time",new Date()));
            list.add(KVPair.build("value","update entity"));

            //task.exeUpdate(bean,list,KVPair.build("id",21));

            //DBBean dbBean1 =  (DBBean) task.exeQueryTID(DBBean.class,1);
            //long count = task.exeQueryCount(DBBean.class,list);
            //List<DBBean> dbBeanList = task.getList(DBBean.class,"select * from db_bean");
            //task.getMap(DBBean.class,"id","create_ime");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }




    @UseTransaction
    public void testTransaction(){
        StatementTask task = new StatementTask();
        try {
            task.exeInsert(new DBBean());
            task.exeUpdate(new DBBean());
            task.exeDelete(DBBean.class,";drop table...");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
